package com.roger.cmdkit.cases;

import com.roger.cmdkit.base.CmdBase;
import com.roger.cmdkit.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Locale;

public class BillAnalysis implements CmdBase {
    private ArrayList<String> number0 = new ArrayList<>();
    private ArrayList<String> income0 = new ArrayList<>();
    private ArrayList<String> cost_pos0 = new ArrayList<>();
    private ArrayList<String> cost_ground0 = new ArrayList<>();
    private ArrayList<String> cost_safe0 = new ArrayList<>();
    private ArrayList<String> cost_air = new ArrayList<>();
    private ArrayList<String> cost_train = new ArrayList<>();
    private ArrayList<String> cost_transport0 = new ArrayList<>();
    private ArrayList<String> cost_other0 = new ArrayList<>();
    private ArrayList<String> profit0 = new ArrayList<>();
    private ArrayList<String> number = new ArrayList<>();
    private ArrayList<String> project = new ArrayList<>();
    private ArrayList<String> income = new ArrayList<>();
    private ArrayList<String> cost_pos = new ArrayList<>();
    private ArrayList<String> cost_ground = new ArrayList<>();
    private ArrayList<String> cost_safe = new ArrayList<>();
    private ArrayList<String> cost_transport = new ArrayList<>();
    private ArrayList<String> cost_other = new ArrayList<>();
    private ArrayList<String> profit = new ArrayList<>();
    private String[] field = {"number","project","income","cost_pos","cost_ground","cost_safe","cost_transport"
            ,"cost_other","profit"};
    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy.MM.dd", Locale.CHINA);
    private SimpleDateFormat sdf0 = new SimpleDateFormat ("yyyy/MM/dd HH:mm:ss",Locale.CHINA);
    private SimpleDateFormat sdf = new SimpleDateFormat ("yyyyMMddHHmmss",Locale.CHINA);

    @Override
    public void cmdS(String[] args) {
        System.out.println("****Bill Analysis Tool_V1.0.1****" );
        File f = new File(System.getProperty("user.dir")+ File.separator + "excel");
        File[] fList = f.listFiles();
        if(fList != null){
            for(File f1 : fList){
                if(f1.isFile()){
                    String fName=f1.getName();
                    if(fName.substring(fName.lastIndexOf("."),fName.length()).equals(".xls")){
                        Date date = new Date();
                        System.out.println(sdf0.format(date)+": start analyse " + fName );

                        number0.clear();number.clear();
                        income0.clear();income.clear();
                        project.clear();
                        cost_pos0.clear();cost_pos.clear();
                        cost_ground0.clear();cost_ground.clear();
                        cost_safe0.clear();cost_safe.clear();
                        cost_air.clear();cost_train.clear();
                        cost_transport0.clear();cost_transport.clear();
                        cost_other0.clear();cost_other.clear();
                        profit0.clear();profit.clear();

                        System.out.println("get data from sheet1");
                        getData0(f1.getAbsolutePath());
                        System.out.println("get data from sheet2");
                        getData(f1.getAbsolutePath());

                        String result0 = System.getProperty("user.dir")+ File.separator + "result" + File.separator + sdf.format(date) +"_original_"+ fName;
                        ExcelUtil.createExcel(result0,field);
                        System.out.println("list sheet2 data to " + result0);
                        for(int i =0;i<number.size();i++){
                            ExcelUtil.addExcelData(result0,0,i+1,(short) 0,number.get(i));
                            ExcelUtil.addExcelData(result0,0,i+1,(short) 1,project.get(i));
                            addExcelData(result0,0,i+1,(short) 2,income.get(i),true);
                            addExcelData(result0,0,i+1,(short) 3,cost_pos.get(i),true);
                            addExcelData(result0,0,i+1,(short) 4,cost_ground.get(i),true);
                            addExcelData(result0,0,i+1,(short) 5,cost_safe.get(i),true);
                            addExcelData(result0,0,i+1,(short) 6,cost_transport.get(i),true);
                            addExcelData(result0,0,i+1,(short) 7,cost_other.get(i),true);
                            addExcelData(result0,0,i+1,(short) 8,profit.get(i),true);
                        }

                        System.out.println("calculate cost_transport = cost_air + cost_train");
                        for(int i=0;i<cost_air.size();i++){
                            float cost = Float.parseFloat(cost_air.get(i))+Float.parseFloat(cost_train.get(i));
                            cost_transport0.add(String.format("%.2f",cost));
                        }

                        String result = System.getProperty("user.dir")+ File.separator + "result" + File.separator + sdf.format(date) +"_analysis_"+ fName;
                        ExcelUtil.createExcel(result,field);
                        System.out.println("comparing sheet2 with sheet1\nwrite result to report " + result);
                        for(int i=0;i<number0.size();i++){
                            for(int j =0;j<number.size();j++){
                                if(number0.get(i).equals(number.get(j))){
                                    ExcelUtil.addExcelData(result,0,i+1,(short) 0,number.get(j));
                                    ExcelUtil.addExcelData(result,0,i+1,(short) 1,project.get(j));
                                    addExcelData(result,0,i+1,(short) 2,income.get(j),income0.get(i).equals(income.get(j)));
                                    addExcelData(result,0,i+1,(short) 3,cost_pos.get(j),cost_pos0.get(i).equals(cost_pos.get(j)));
                                    addExcelData(result,0,i+1,(short) 4,cost_ground.get(j),cost_ground0.get(i).equals(cost_ground.get(j)));
                                    addExcelData(result,0,i+1,(short) 5,cost_safe.get(j),cost_safe0.get(i).equals(cost_safe.get(j)));
                                    addExcelData(result,0,i+1,(short) 6,cost_transport.get(j),cost_transport0.get(i).equals(cost_transport.get(j)));
                                    addExcelData(result,0,i+1,(short) 7,cost_other.get(j),cost_other0.get(i).equals(cost_other.get(j)));
                                    addExcelData(result,0,i+1,(short) 8,profit.get(j),profit0.get(i).equals(profit.get(j)));
                                }
                            }
                        }
                        System.out.println("write result to report finished!");
                    }
                }
            }
        }
    }


    private void getData0(String file) {
        HSSFSheet st = ExcelUtil.getSheet(file,0);
        int rows = ExcelUtil.getAllRows(file, 0);
        for(int i = 1; i <=rows; i++){
            if(st != null) {
                HSSFRow row = st.getRow(i);
                HSSFCell num= row.getCell((short) 0);
                if(num!=null && !getValue(num).equals("")){
                    String s = getValue(num);
                    number0.add(s.replace(".00",""));
                }
                HSSFCell inc = row.getCell((short) 2);
                HSSFCell pos = row.getCell((short) 3);
                HSSFCell ground = row.getCell((short) 4);
                HSSFCell safe = row.getCell((short) 5);
                HSSFCell air = row.getCell((short) 6);
                HSSFCell train = row.getCell((short) 7);
                HSSFCell other = row.getCell((short) 8);
                HSSFCell prof = row.getCell((short) 9);
                if(!getValue(inc).equals("")) income0.add(getValue(inc));
                if(!getValue(pos).equals("")) cost_pos0.add(getValue(pos));
                if(!getValue(ground).equals("")) cost_ground0.add(getValue(ground));
                if(!getValue(safe).equals("")) cost_safe0.add(getValue(safe));
                if(!getValue(air).equals("")) cost_air.add(getValue(air));
                if(!getValue(train).equals("")) cost_train.add(getValue(train));
                if(!getValue(other).equals("")) cost_other0.add(getValue(other));
                if(!getValue(prof).equals("")) profit0.add(getValue(prof));
            }
        }
    }

    private void getData(String file) {
        HSSFSheet st = ExcelUtil.getSheet(file,1);
        int rows = ExcelUtil.getAllRows(file, 1);
        for(int i = 4; i <=rows; i++){
            if(st != null) {
                HSSFRow row = st.getRow(i);
                HSSFCell num= row.getCell((short) 0);
                if(num!=null && !getValue(num).equals("")){
                    String s = getValue(num);
                    number.add(s.replace(".00",""));
                }
                HSSFCell date= row.getCell((short) 1);
                HSSFCell line= row.getCell((short) 2);
                if (date!=null && line != null && !getValue(date).equals("")) {
                    String pro =getValue(date)+getValue(line);
                    project.add(pro);
                }
                try {
                    HSSFCell prof = row.getCell((short) 18);
                    if(prof!=null && !getValue(prof).equals("")){
                        HSSFCell inc = row.getCell((short) 11);
                        HSSFCell pos = row.getCell((short) 15);
                        HSSFCell ground = row.getCell((short) 13);
                        HSSFCell safe = row.getCell((short) 14);
                        HSSFCell tran = row.getCell((short) 12);
                        HSSFCell other = row.getCell((short) 16);
                        income.add(getValue(inc));
                        cost_pos.add(getValue(pos));
                        cost_ground.add(getValue(ground));
                        cost_safe.add(getValue(safe));
                        cost_transport.add(getValue(tran));
                        cost_other.add(getValue(other));
                        profit.add(getValue(prof));
                    }
                }catch (Throwable e){
                    e.printStackTrace();
                }
            }
        }
    }

    private String getValue(HSSFCell cell){
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (date != null) {
                        return dateFormat.format(date);
                    } else return "";
                } else {
                    return String.format("%.2f",cell.getNumericCellValue());
                }
            case HSSFCell.CELL_TYPE_FORMULA:
                if (!cell.getStringCellValue().equals("")) {
                    return cell.getStringCellValue();
                } else {
                    return String.format("%.2f",cell.getNumericCellValue());
                }
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return  (cell.getBooleanCellValue() ? "Y" : "N");
            default:
                return "";
        }
    }

    private void addExcelData(String file,int sheet,int rows,short cols,String text,boolean isRight) {
        try {
            File f = new File(file);
            FileInputStream fis = new FileInputStream(f);
            BufferedInputStream bis = new BufferedInputStream(fis);
            POIFSFileSystem fs = new POIFSFileSystem(bis);
            HSSFWorkbook wb = new HSSFWorkbook(fs);

            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short)12);
            font.setFontName("Times New Roman");
            if(!isRight) font.setColor(HSSFFont.COLOR_RED);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
            style.setFont(font);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);

            HSSFSheet st = wb.getSheetAt(sheet);
            HSSFRow row = st.createRow(rows);
            HSSFCell cell = row.createCell(cols);
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            //cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            if(text.equals("")) text="null";
            cell.setCellStyle(style);
            cell.setCellValue(text);

            FileOutputStream fos = new FileOutputStream(file);
            wb.write(fos);
            fos.close();
            bis.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
